package com.ocean.common.utils.file;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.usermodel.Picture;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.w3c.dom.Document;

/**
* Title: PoiExcelToHtml
* Description:   excel 转html
* @author dingxy
* @date 2019年1月17日
*/
public class PoiExcelToHtmlUtils {

	public static String PoiExcelToHtml(String filepath,String outPath,String oldFileName){
    	String suffixName = oldFileName.substring(oldFileName.lastIndexOf(".")+1);   // 获取后缀类型(doc或者docx)
    	String endPath = "";
    	if(suffixName.equals("xls")){
    		endPath = PoiExcel03ToHtml(filepath,outPath,oldFileName);
    	}else{
    		endPath = Excel07ToHtml(filepath,outPath,oldFileName);
    	}
		return endPath;
	}


    /**
     * Title: PoiExcel03ToHtml
     * Description:  03 Excel 转为 Html
     * @param filepath 	原预览文件的路径
     * @param outPath	生成html文件路径
     * @param oldFileName 预览文件的名称
     * @throws Exception
     */
    public static String PoiExcel03ToHtml (String filepath,String outPath,String oldFileName) {

    	String prefixFileName = oldFileName.substring(0,oldFileName.lastIndexOf(".")); //获取前缀名
    	// String suffixName = oldFileName.substring(oldFileName.lastIndexOf(".")+1);   // 获取后缀类型(doc或者docx)

    	String htmlname = prefixFileName+".html";  // 最终生成的html 的名称
		//filepath = "D:\\cloud\\ocean\\NMF\\EXCEL\\";

		fileExists(outPath);//此方法是判断目录文件夹是否存在
	    //InputStream input = new FileInputStream(filepath+"/"+oldFileName);
	    InputStream input = null;
	    HSSFWorkbook excelBook = null;
	    String endPath = "";
		try {
			input = new FileInputStream(filepath);
			excelBook = new HSSFWorkbook(input);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			return "";
		}catch (IOException e) {
			e.printStackTrace();
			return "";
		}

	    for (int numSheet = 0; numSheet < excelBook.getNumberOfSheets(); numSheet++) {
            Sheet sheet = excelBook.getSheetAt(numSheet);
            System.out.println(sheet.getSheetName());
            if (sheet.getRow(sheet.getFirstRowNum()) == null) {
                continue;
            }

            ExcelToHtmlConverter excelToHtmlConverter = null;
			try {
				excelToHtmlConverter = new ExcelToHtmlConverter (DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument() );
				excelToHtmlConverter.setOutputColumnHeaders(false);  //去掉Excel头行
	            excelToHtmlConverter.setOutputRowNumbers(false);   //去掉Excel行号
	            excelToHtmlConverter.processWorkbook(excelBook); //excel转html
			} catch (ParserConfigurationException e) {
				e.printStackTrace();
				return "";
			}


            // excel 中图片的处理
    	    /*List<HSSFPictureData> pics = excelBook.getAllPictures();
    	    if (pics != null) {
    	        for (int i = 0; i < pics.size(); i++) {
    	            HSSFPictureData pic = (HSSFPictureData) pics.get (i);
    	            String ext = pic.suggestFileExtension(); // 获取图片类型
    	            byte[] data = pic.getData();
    	            if (ext.equals("jpeg")) {
    	                FileOutputStream out = new FileOutputStream( path + 0 + ".jpg");
    	                out.write(data);
    	                out.close();
    	            }
    	            if (ext.equals("png")) {
    	                FileOutputStream out = new FileOutputStream( path + 0 + ".png");
    	                out.write(data);
    	                out.close();
    	            }
    	        }
    	    }*/


    	    Document htmlDocument =excelToHtmlConverter.getDocument();
    	    ByteArrayOutputStream outStream = new ByteArrayOutputStream();//字节数组输出流
    	    DOMSource domSource = new DOMSource (htmlDocument);
    	    StreamResult streamResult = new StreamResult (outStream);
    	    /** 将document中的内容写入文件中，创建html页面 */
    	    TransformerFactory tf = TransformerFactory.newInstance();
    	    Transformer serializer;
    	    String content="";
			try {
				serializer = tf.newTransformer();
				serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8");
		    	serializer.setOutputProperty (OutputKeys.INDENT, "yes");
		    	serializer.setOutputProperty (OutputKeys.METHOD, "html");
		    	serializer.transform (domSource, streamResult);
		    	outStream.close();
		    	content = new String (outStream.toString("UTF-8"));
		    	FileUtils.writeStringToFile(new File (outPath, htmlname), content, "utf-8");
			} catch (TransformerConfigurationException e) {
				e.printStackTrace();
				return "";
			} catch (TransformerException e) {
				e.printStackTrace();
				return "";
			}catch (IOException e) {
				e.printStackTrace();
				return "";
			}
        }

	    // 保证上面的excel 能按时执行完
	    try {
			Thread.sleep(15000);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}

	    String endPathTemp = outPath+ htmlname;
        String attrstemp =endPathTemp.replace("\\", ",").replace("/",",").replace("//",",");
        String[] attrs = attrstemp.split(",");
        // 对路径进行截取
        //String[] attrs =endPathTemp.split("\\\\");
        String name0 = attrs[attrs.length-4];
        String name1 = attrs[attrs.length-3];
		String name2 = attrs[attrs.length-2];
		String name3 = attrs[attrs.length-1];
		endPath = name0+File.separator+name1+File.separator+name2+File.separator+name3;
		System.out.println(endPath);
		return endPath;
	 }


    // 判断目录文件夹是否存在
    private static void fileExists(String path) {
		File file =new File(path);
		if(!file.exists()){
			file.mkdirs();
		}
	}


    /**
     * Title: Excel07ToHtml
     * Description:  excel07转html
     * @param filepath  文件的路径
     * @param outPath   生成的html 路径
     * @param oldFileName 预览文件的名称
     * @throws Exception
     */
    public static String Excel07ToHtml (String filepath,String outPath,String oldFileName){

    	String prefixFileName = oldFileName.substring(0,oldFileName.lastIndexOf(".")); //获取前缀名
    	String suffixName = oldFileName.substring(oldFileName.lastIndexOf(".")+1);   // 获取后缀类型(doc或者docx)
    	String htmlname = prefixFileName+".html";  // 最终 生成的html 名称

	 	fileExists(outPath);
	  	Workbook workbook = null;
	  	//InputStream inputStream = new FileInputStream(filepath+"/"+oldFileName);
	  	InputStream inputStream = null;
		try {
			inputStream = new FileInputStream(filepath);
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
			return "";
		}

		String endPath="";
        try {
        	//String html="";
        	StringBuilder sb =new StringBuilder("");
            workbook =  new XSSFWorkbook(inputStream);
            for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
                Sheet sheet = workbook.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                //POI设置上下左右居中：
                //html+="=======================" + sheet.getSheetName() + "=========================<br><br>";
                sb.append("=======================" + sheet.getSheetName() + "=========================<br><br>");
                sheet.setColumnWidth(numSheet, 200 * 256);

                int firstRowIndex = sheet.getFirstRowNum();
                int lastRowIndex = sheet.getLastRowNum();
                //html+="<table border='1' align='center'>";
                sb.append("<table border='1' align='center'>");
                Row firstRow = sheet.getRow(firstRowIndex);
                if(firstRow==null ){ // 如果工作薄没有内容，直接跳过
                	continue ;
                }

                for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) {
                    Cell cell = firstRow.getCell(i);
                    String cellValue = getCellValue(cell, true);
                    //html+="<th>" + cellValue + "</th>";
                    sb.append("<th>" + cellValue + "</th>");
                }

                //行
                for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
                    Row currentRow = sheet.getRow(rowIndex);
                    //html+="<tr>";
                    sb.append("<tr>");
                    if(currentRow!=null){
                    	currentRow.setHeight((short) 1000);
                    	int firstColumnIndex = currentRow.getFirstCellNum();
                    	int lastColumnIndex = currentRow.getLastCellNum();
                    	//列
                    	for (int columnIndex = firstColumnIndex; columnIndex <= lastColumnIndex; columnIndex++) {
                    		Cell currentCell = currentRow.getCell(columnIndex);
                    		String currentCellValue = getCellValue(currentCell, true);
                    		//html+="<td>"+currentCellValue + "</td>";
                    		sb.append("<td>"+currentCellValue + "</td>");
                    	}
                    }else{
                    	 //html+=" ";
                    	 sb.append(" ");
                    }
                    //html+="</tr>";
                    sb.append("</tr>");
                }
                //html+="</table>";
                sb.append("</table>");
                //html+="<br/><br/>";
                sb.append("<br/><br/>");


                ByteArrayOutputStream outStream = new ByteArrayOutputStream();
       	     	DOMSource domSource = new DOMSource ();
       	     	StreamResult streamResult = new StreamResult (outStream);

       	     	TransformerFactory tf = TransformerFactory.newInstance();
       	     	Transformer serializer = tf.newTransformer();
       	     	serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8");
       	     	serializer.setOutputProperty (OutputKeys.INDENT, "yes");
       	     	serializer.setOutputProperty (OutputKeys.METHOD, "html");
       	     	serializer.transform (domSource, streamResult);
       	     	outStream.close();
       	     	//FileUtils.writeStringToFile(new File (path, htmlname), html, "utf-8");
       	     	FileUtils.writeStringToFile(new File (outPath, htmlname), sb.toString(), "utf-8");


            }
        } catch (Exception e) {
            e.printStackTrace();
            return "";
        }

        // 保证上面的excel 能按时执行完
	    try {
			Thread.sleep(15000);
		} catch (InterruptedException e) {
			e.printStackTrace();
		}

        String endPathTemp = outPath+ htmlname;
        String attrstemp =endPathTemp.replace("\\", ",").replace("/",",").replace("//",",");
        String[] attrs = attrstemp.split(",");
        // 对路径进行截取
        //String[] attrs =endPathTemp.split("\\\\");
        String name0 = attrs[attrs.length-4];
        String name1 = attrs[attrs.length-3];
		String name2 = attrs[attrs.length-2];
		String name3 = attrs[attrs.length-1];
		endPath = name0+File.separator+name1+File.separator+name2+File.separator+name3;
		System.out.println(endPath);
		return endPath;
    }

    /**
     * Title: getCellValue
     * Description:  读取单元格
     * @param cell
     * @param treatAsStr
     * @return
     */
    private static String getCellValue(Cell cell, boolean treatAsStr) {
        if (cell == null) {
            return "";
        }
        if (treatAsStr) {
            cell.setCellType(CellType.STRING);
        }
        if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }


    public static void main(String[] args) throws IOException, ParserConfigurationException, TransformerException {
    	try {
			Excel07ToHtml("D:\\cloud\\ocean\\NMF\\EXCEL\\exportExcel.xlsx","D:\\cloud\\ocean\\NMF\\EXCEL\\","exportExcel.xlsx");
		} catch (Exception e) {
			e.printStackTrace();
		}

    	/*try {
    		PoiExcel03ToHtml("D:\\cloud\\ocean\\NMF\\EXCEL\\11.xls","D:\\cloud\\ocean\\NMF\\EXCEL\\","11.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}*/

	}

}
